CREATE OR REPLACE EDITIONABLE FUNCTION "SCOTT"."FNC_JUDGE_BY_MACD_GOLD_CROSS" (p_date       in varchar2,
                                                        p_stock_code in varchar2)
  return number is
  /** 判断某只股票在某一日，MACD是否是金叉。如果是金叉，则返回1，如果不是金叉，则返回-1 **/
  result number;
  -- 当日的dif
  v_current_dif number;
  -- 当日的dea
  v_current_dea number;
  -- 前一日的dif
  v_last_dif number;
  -- 前一日的dea
  v_last_dea number;
begin
  begin
    -- 计算当日的dif、dea
    select t.dif, t.dea
      into v_current_dif, v_current_dea
      from stock_transaction_data_all t
     where t.code_ = p_stock_code
       and t.date_ = to_date(p_date, 'yyyy-mm-dd');

    -- 计算前一日的dif、dea
    select b.dif, b.dea
      into v_last_dif, v_last_dea
      from (select *
              from stock_transaction_data_all t
             where t.code_ = p_stock_code
               and t.date_ < to_date(p_date, 'yyyy-mm-dd')
             order by t.date_ desc) b
     where rownum <= 1;
  exception
    when no_data_found then
      DBMS_OUTPUT.put_line('代码为【' || p_stock_code || '】的股票，
            在【' || p_date || '】或者前一天没有dif和dea');
      result := -1;
      return(result);
  end;

  -- 判断是否是金叉
  if v_last_dif <= v_last_dea and v_current_dif >= v_current_dea then
    result := 1;
    return(result);
  else
    result := -1;
    return(result);
  end if;

end fnc_judge_by_macd_gold_cross;